/*
 * VendorItemDAO.java
 *
 * Created on March 29, 2007, 9:13 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.samlam;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 *
 * @author BrianB
 */
public class VendorItemDAO extends BusinessObject{
    
     //singleton pattern
    private static VendorItemDAO instance = null;
    
    /** Creates a new instance of VendorItemDAO */
    public VendorItemDAO() {
    }
    
    /**
     * enforcement of the singleton pattern
     * @return the instance which will be used when the DAO is used
     */
    public static synchronized VendorItemDAO getInstance() {
        if (instance == null) {
            instance = new VendorItemDAO();
        }
        return instance;
    }
    
    
    //////////////////////////
    //CREATE
    /**
     * This method is used to create a business object of type VendorItem.
     * @return VendorItemBO
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors in obtaining the GUID
     */
    public VendorItem create() throws DataException {
        String id = null;
        try{
            id = GUID.generate();
        } catch (Exception e) {
            throw new DataException("Could not generate new VendorItem", e);
        }
        VendorItem vi = new VendorItem(id);
        Cache c = Cache.getInstance();
        c.put(vi.getViguid(), vi);
        return vi;
    }
    
    //////////////////////////
    //READ
    /**
     * This method reads the VendorItem object from the guid.
     * @param id the GUID for a particular VendorItem to be read
     * @return VendorItemBO with the matching id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is an error with the SQL or the connection
     */
    public synchronized VendorItem read(String id) throws DataException, ConnectionPoolException {
               
        VendorItem vi = (VendorItem)Cache.getInstance().get(id);
        
        if (vi == null) {
        Connection conn = null;
            try {
                conn = ConnectionPool.getInstance().get();

                vi = read(id, conn);

                conn.commit();
                ConnectionPool.getInstance().release(conn);                
            }
            catch (DataException e) {
                throw new DataException("Could not get a connection to the database.");
            }
            catch (SQLException e) {
                //rollback
                try {
                    conn.rollback();
                    ConnectionPool.getInstance().release(conn);
                }
                catch (SQLException e2) {
                    throw new DataException("ERROR: Could not even release the connection", e2);
                }
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
        }
        
        return vi;
    }
    
    /**
     * This method reads the VendorItem object with the id and a connection
     * @param id the GUID for a particular VendorItem to be read
     * @param conn the connection to use for the reading
     * @return VendorItemBO with the specified id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is any error in the reading
     * @throws java.sql.SQLException if there is any error in the SQL
     */
    public synchronized VendorItem read(String id, Connection conn) throws DataException, SQLException {                  //will also create a BO, but it's pulling it off the database and creating the object to be used
        VendorItem vi = (VendorItem)Cache.getInstance().get(id);
        
        if (vi == null) {
                   
            try {   
                PreparedStatement SELECT = conn.prepareStatement("SELECT * FROM APP.vendoritem WHERE viguid = ?");
                SELECT.setString(1, id);
                ResultSet rs = SELECT.executeQuery();
                
                conn.commit();
                
                if (rs.next()) {
                    vi = new VendorItem(id);
                    vi.setCost(rs.getDouble("cost"));
                    vi.setVendor(VendorDAO.getInstance().read(rs.getString("vguid")));
                    vi.setConceptual(ConceptualDAO.getInstance().read(rs.getString("cpguid")));
                    Cache.getInstance().put(id, vi);
                    
                    vi.setAlreadyInDB(true);
                }
                else {
                    throw new DataException("Object was not found in the database.");
                }

            }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
            }catch (Exception e) {
                throw new DataException("error here...", e);
            }
        }
        
        return vi;
    }
    
    /**
     * This method gets the connection and puts the VendorItem into the database.
     * @param c the VendorItemBO to be saved
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    public synchronized void save(VendorItem vi) throws SQLException, DataException, ConnectionPoolException {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            System.out.println("Going to save with conn");
            save(vi, conn);
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        }
        catch (DataException e) {
            conn.rollback();
            throw new DataException("Could not connect to the database.", e);
        }
        
    }
    
    /**
     * This method puts the VendorItem into the database, either inserting
     * or updating depending on whether it exists in the database already.
     * @param vi the VendorItemBO to be saved
     * @param conn the connection to be used
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    synchronized void save(VendorItem vi, Connection conn) throws SQLException, DataException {
        if (vi.isAlreadyInDB()){
            System.out.println("updating...");
            update(vi, conn);    
        }   
        else {
            System.out.println("inserting...");
            insert(vi, conn);
            
        }
        vi.setDirty(false);
    }
    
    /**
     * This method saves the updated VendorItem to the database if it already exists.
     * @param vi the VendorItemBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the update
     */
    void update(VendorItem vi, Connection conn) throws SQLException, DataException {
        try {
            System.out.println("VIGUID:" + vi.getViguid());
            PreparedStatement UPDATE = conn.prepareStatement("UPDATE APP.vendoritem SET cost = ?, vguid = ?, cpguid = ? WHERE viguid = ?");
            UPDATE.setDouble(1, vi.getCost());
            UPDATE.setString(2, vi.getVendor().getVguid());
            UPDATE.setString(3, vi.getConceptual().getCpguid());
            UPDATE.setString(4, vi.getViguid());
            UPDATE.executeUpdate();            
            System.out.println("Update executed");
            UPDATE.close();
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not perform the update on the VendorItem", e);
        }
    }
    
    /**
     * This method saves the VendorItem into the database if it did not previously 
     * exist.
     * @param vi the VendorItemBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the insert
     */
    public void insert(VendorItem vi, Connection conn) throws SQLException, DataException {
        try {
            PreparedStatement INSERT = conn.prepareStatement("INSERT into APP.vendoritem VALUES (?, ?, ?, ?)" );
            INSERT.setString(1, vi.getViguid());
            INSERT.setDouble(2, vi.getCost());
            INSERT.setString(3, vi.getVendor().getVguid());
            INSERT.setString(4, vi.getConceptual().getCpguid());
            
            INSERT.executeUpdate();
            INSERT.close();
            
            vi.setAlreadyInDB(true);
            
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not insert the VendorItem into the database", e);
        }
    }
    
    
    /////////////////////////////////DELETE//////////////////////////////////////////////////
    //A delete method was intentionally omitted here for business reasons--we would like
    //to keep a record of all vendoritems Because they are attached to vendors, products and other essential elements
    //that should not be deleted from the database.  Rather than deleting them completely,
    //we might instead build in a "deleted" boolean variable where we can flag a customer if they are 
    //no longer active.
    
}
